package com.akk.utils;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonParser;
import com.google.gson.reflect.TypeToken;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Type;
import java.util.Map;

@Service
public class WriteExcel {

    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
    private int DEFAULT_COLOUMN_WIDTH = 17;

    @Value("${temp.path}")
    private String tempPath;

    /**
     * 导出Excel
     * @param title Excel名称
     * @param dataStr 内容JSON字符串
     * @param columnWidth 列宽（默认17）
     * @param titles 列名
     * @param replace 替换指定值
     * @param columns 需要列
     * @return File
     * */
    public File exportExcel(String title, String dataStr,String[] titles,Map<String,Map<String,String>> replace,Map<Integer,Integer> columnWidth,String[] columns) {
        try {
            File file = new File(tempPath + title + ".xls");
            WritableWorkbook writableWorkbook = Workbook.createWorkbook(file);
            WritableSheet sheet = writableWorkbook.createSheet("第一页",0);
            JsonArray jsonArray = new JsonParser().parse(dataStr).getAsJsonArray();
            // 设置列宽
            if (columnWidth != null){
                for (Integer index:columnWidth.keySet()){
                    sheet.setColumnView(index,columnWidth.get(index));
                }
            }else {
                for (int index = 0;index<titles.length;index++){
                    sheet.addCell(new Label(index,0,titles[index]));
                    sheet.setColumnView(index,DEFAULT_COLOUMN_WIDTH);
                }
            }
            // 设置title
            for (int index = 0;index<titles.length;index++){
                sheet.addCell(new Label(index,0,titles[index]));
            }

            for (int i = 0;i < jsonArray.size();i++) {
                Type type = new TypeToken<Map<String,Object>>(){}.getType();
                Map object = new Gson().fromJson(jsonArray.get(i),type);
                Object[] keys = object.keySet().toArray();
                if (columns != null && columns.length > 0)
                    keys = columns;
                for (int index = 0;index<keys.length;index++) {
                    String key = keys[index].toString();
                    String value = object.get(key) == null ? "" : object.get(keys[index]).toString();
                    // 替换
                    if (replace != null && replace.containsKey(key)){
                        Map<String,String> values = replace.get(key);
                        value = values.get(value);
                    }
                    Label label = new Label(index,i+1,value);
                    sheet.addCell(label);
                }
            }
            writableWorkbook.write();
            writableWorkbook.close();
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }



//    /**
//     * 导出Excel 2007 OOXML (.xlsx)格式
//     * @param title 标题行
//     * @param headMap 属性-列头
//     * @param jsonArray 数据集
//     * @param datePattern 日期格式，传null值则默认 年月日
//     * @param colWidth 列宽 默认 至少17个字节
//     * @param out 输出流
//     */
//    public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {
//        if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;
//        // 声明一个工作薄
//        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
//        workbook.setCompressTempFiles(true);
//        //表头样式
//        CellStyle titleStyle = workbook.createCellStyle();
//        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        Font titleFont = workbook.createFont();
//        titleFont.setFontHeightInPoints((short) 20);
//        titleFont.setBoldweight((short) 700);
//        titleStyle.setFont(titleFont);
//        titleStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
//        SXSSFSheet sheet = workbook.createSheet();
//        //设置列宽
//        int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
//        int[] arrColWidth = new int[headMap.size()];
//        // 产生表格标题行,以及设置列宽
//        String[] properties = new String[headMap.size()];
//        String[] headers = new String[headMap.size()];
//        int ii = 0;
//        for (Iterator<String> iter = headMap.keySet().iterator(); iter
//                .hasNext();) {
//            String fieldName = iter.next();
//
//            properties[ii] = fieldName;
//            headers[ii] = headMap.get(fieldName);
//
//            int bytes = fieldName.getBytes().length;
//            arrColWidth[ii] =  bytes < minBytes ? minBytes : bytes;
//            sheet.setColumnWidth(ii,arrColWidth[ii]*256);
//            ii++;
//        }
//        // 遍历集合数据，产生数据行
//        int rowIndex = 0;
//        for (Object obj : jsonArray) {
//            if(rowIndex == 65535 || rowIndex == 0){
//                if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
//
//                SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
//                titleRow.createCell(0).setCellValue(title);
//                titleRow.getCell(0).setCellStyle(titleStyle);
//                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
//
//                SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
//                for(int i=0;i<headers.length;i++)
//                {
//                    headerRow.createCell(i).setCellValue(headers[i]);
////                    headerRow.getCell(i).setCellStyle(headerStyle);
//
//                }
//                rowIndex = 2;//数据内容从 rowIndex=2开始
//            }
//            JSONObject jo = (JSONObject) obj;
//            SXSSFRow dataRow = sheet.createRow(rowIndex);
//            for (int i = 0; i < properties.length; i++)
//            {
//                SXSSFCell newCell = dataRow.createCell(i);
//
//                Object o =  jo.get(properties[i]);
//                String cellValue = "";
//                if(o==null) cellValue = "";
//                else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
//                else if(o instanceof Float || o instanceof Double)
//                    cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
//                else cellValue = o.toString();
//
//                newCell.setCellValue(cellValue);
////                newCell.setCellStyle(cellStyle);
//            }
//            rowIndex++;
//        }
//        // 自动调整宽度
//        /*for (int i = 0; i < headers.length; i++) {
//            sheet.autoSizeColumn(i);
//        }*/
//        try {
//            workbook.write(out);
//            workbook.close();
//            workbook.dispose();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }

}
